"""Python 处理电子表格示例
"""

import openpyxl
# 对文件的操作
wb = openpyxl.load_workbook('excel_ex.xlsx')


# 建立新的工作簿，自动建立一个表，表名"Sheet"
wb_new = openpyxl.Workbook()

# 保存
wb_new.save('new_test.xlsx')

# 对工作表的操作
print(wb.sheetnames)

print("对工作表遍历")
for sheet in wb:
    print(sheet.title)

print(wb.worksheets)

# 新建一个工作表
#sheet1 = wb.create_sheet("新的工作表")
# 保存到原文件中
#sheet1 = wb["新的工作表"]
#wb.remove(sheet1)
#wb.save('exercise/excel_ex.xlsx')

sheet2 = wb['student']
print(f"行数{sheet2.max_row}, 列数{sheet2.max_column}")

# 对单元格的操作
#选行
#cells = sheet2[3:4]
#print(cells)
# 选列
#cells = sheet2['B:D']
#print(cells)


cells = sheet2['A2:D3']
print(cells)
# 对单元格的操作
# 选取，读值，写入

for rows in cells:
    for cell in rows:
        cell.value = 123


cell = sheet2['A1']
print(cell, cell.value)

print("第1行第6列的值是", sheet2.cell(1, 6).value)

for x in range(1, 10):
    for y in range(1, 5):
        sheet2.cell(x, y).value = x*y
sheet2.insert_rows(5, 3)
# sheet2.insert_cols(5, 3)
# sheet2.delete_rows(5, 3)
# sheet2.delete_cols(5, 3)
wb.save('excel_ex3.xlsx')
